"""
title: Anyquery
author: julien040
author_url: https://github.com/julien040
git_url: https://github.com/julien040/anyquery.git
description: Query, and insert/update/delete data from local/SaaS apps (e.g. Airtable, Google Sheets, GitHub, Spotify, etc.) using SQL.
"""

# Not working because OpenWebUI seems to not expose the function description
# It cannot be fixed, use another tool

from pydantic import BaseModel, Field
from requests import post, get


endpoint = "https://gpt-actions.anyquery.xyz/"


class Tools:
    def __init__(self):
        self.valves = self.Valves()

    class Valves(BaseModel):
        anyquery_id: str = Field(
            "", description="Your Anyquery ID generated by the anyquery gpt command")

    def list_tables_for_data(self) -> str:
        """List all the SQL tables you have access to. They contain data from local/SaaS apps (e.g. Airtable, Google Sheets, GitHub, Spotify, etc.).
        When a user requests some data (from a SaaS/local app), or you need additional context, call list_tables_for_data to check if you can find this information in one of the tables.
        For example:
            - Can you check my GitHub assigned issues?
            - Can you list the tracks of my Spotify playlist?
            - Can you summary my calendar events for today?
            - I need help prioritizing my to-do list.

        Returns:
            str: List of all the tables you have access to, with their descriptions.
        """

        url = endpoint + "list-tables"
        response = get(
            url, headers={"Authorization": "Bearer " + self.valves.anyquery_id})

        return response.text()

    def describe_table(self, table: str) -> str:
        """Get the schema of a table after a call to list_tables_for_data, and before running a query.
        Before running a query, call list_tables_for_data to check if the table exists, and then call describe_table for each table to understand the schema.

        Args:
            table (str): The table name.

        Returns:
            str: A text description of the table schema, with its parameters and types.
        """

        url = endpoint + "describe-table"
        response = post(url, headers={
                        "Authorization": "Bearer " + self.valves.anyquery_id}, json={"table_name": table})

        return response.text()

    def run_query_for_data(self, query: str) -> str:
        """Run a SQL query on data from local/SaaS apps (e.g. github, notion, spotify, etc.). Run list_tables_for_data to know which tables you have access to, and make a call to describe_table to each table in the query before running the query.
        Run a SQL query on a table. You can use SELECT, INSERT, UPDATE, DELETE, etc.

        When a user requests data, you can run a SQL query to get the data from the tables you have access to. You MUST call list_tables_for_data to know which tables you have access to, and make a call to describe_table to each table in the query before running the query.

        Before calling this function, call list_tables_for_data to know which tables you have access to, and make a call to describe_table to each table in the query.
        This will help you to understand the schema and the data you are working with.

        Anyquery uses the concept of table parameters like in SQLite. When you describeTable, you might come across fields that are specified as parameter, or required parameters.
        Required parameters must be passed in the table argument for SELECT queries (`SELECT * FROM table(arg1, ..., argn)`, in the WHERE condition for UPDATE/DELETE, and in `VALUES` for INSERTs.
        For example, for the table github_repositories_from_user, you'll run `SELECT * FROM github_repositories_from_user('torvalds');` because the column user is set as a required parameter.

        The omission of a required parameter will result in the `no query solution` error. If this error appears, double-check the parameters of the queried tables.

        When you run describeTable, you might come across examples where the table name differs from the one you passed in the parameters of describeTable. You must still use the table name in the parameters.

        You may uses JOIN, WHERE conditions, LIMIT clauses to reduce the amount of data sent.

        When a user requests data and the filtered column is not a parameter, use `lower` on both sides of the `=` to make a non-case-sensitive comparison. (e.g. `SELECT * FROM myTable WHERE lower(lang) = lower('userValue');`)
        Use the `CONCAT` function to concat strings. Do not use `||`.

        Some columns might be returned as a JSON object or a JSON array. To extract a field of a JSON object, use the `->>` operator, and pass the JSON path (e.g. `SELECT col1 ->> ' $.myField.mySubField'`).
        For a JSON array, you may also use the `->>` operator. You can also create a new table with one row per element of the JSON array using the `json_each` table (e.g.  `SELECT j.value, a.* FROM myTable a, json_each(a.jsonArrayField)`); Finally, you can filter by the value of a JSON array using the `json_has` function (e.g. SELECT * FROM myTable WHERE json_has(jsonArrayField, 5) -- Returns rows where the JSON array jsonArrayField contains 5;`).

        To reduce the amount of data transferred, please specify the column name in the SELECT statements. Avoid using the `*` wildcard for the columns as much as possible.

        You have access to all the functions of the SQLite standard library such as the math functions, date functions, json functions, etc.

        To handle datetime, use the `datetime(time-value, modifier, modifier, ...)` of SQLite. If no `time-value` is specified, it defaults to the current time. It supports several modifiers ± (e.g. `+ 7 years`,`- 2 months`,`+ 4 days`,`- 3 hours`,`+ 7 minutes`, `+ 32 seconds`.

        Column names and table names with backticks. For example, SELECT `Équipe` FROM `my_table`;

        Args:
            query (str): The SQL query.

        Returns:
            str: A markdown table with the query results.
        """

        url = endpoint + "execute-query"
        response = post(url, headers={
                        "Authorization": "Bearer " + self.valves.anyquery_id}, json={"query": query})

        return response.text()
